import logging
import pandas as pd
from sqlalchemy import create_engine

all_city_zp_df=pd.read_csv('../../全国-热门城市岗位数据.csv',encoding='utf8',error_bad_lines=False)
all_city_zp_df.drop_duplicates(inplace=True)
print(all_city_zp_df)
for x in all_city_zp_df.index:
  if 'K' not in all_city_zp_df.loc[x, "job_salary"]:
      print(all_city_zp_df.loc[x, "job_salary"])
      all_city_zp_df.drop(x, inplace = True)
all_city_zp_salary_df=all_city_zp_df['job_salary'].str.split('K',expand=True)[0].str.split('-',expand=True)
all_city_zp_salary_df=all_city_zp_salary_df.rename(columns={0:"salary_lower",1:"salary_high"})
def fun_work_year(x):
    if x in "1-3年":
        return 1
    elif x in "3-5年":
        return 2
    elif x in "5-10年":
        return 3
    elif x in "10年以上":
        return 4
    else:
        return 0
all_city_zp_df['work_year'] = all_city_zp_df['work_year'].apply(lambda x: fun_work_year(x))
# 对`企业规模`字段进行预处理。要求：500人以下：0，500-999：1，1000-9999：2，10000人以上：3
def fun_com_size(x):
    if x in "500-999人":
        return 1
    elif x in "1000-9999人":
        return 2
    elif x in "10000人以上":
        return 3
    else:
        return 0
benefits=all_city_zp_df['job_benefits']
print(benefits)

clean_all_city_zp_df=pd.concat([all_city_zp_df,all_city_zp_salary_df],axis=1)

clean_all_city_zp_df.drop('job_salary',axis=1,inplace=True)
# 对缺失值所在行进行清洗。
clean_all_city_zp_df.dropna(axis=0, how='any', inplace=True)
clean_all_city_zp_df.drop(axis=0,
                          index=(clean_all_city_zp_df.loc[(clean_all_city_zp_df['job_benefits'] == 'None')].index),
                          inplace=True)
# 将处理后的数据保存到 MySQL 数据库
engine = create_engine('mysql+pymysql://root:123456@localhost:3306/bosszp_db?charset=utf8mb4')
clean_all_city_zp_df.to_sql('t_boss_zp_info', con=engine, if_exists='replace')
logging.info("Write to MySQL Successfully!")

